package com.yunkouan.quarantine.controller;

import com.yunkouan.quarantine.entity.Flight;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.util.StringUtils;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
import java.util.regex.Pattern;

/**
 * Created by zhouxiaoyuan on 2017/10/26.
 */
public class SQLgenerator {

    public static void main(String[] args){

        String fileInput = "D:/mbg/design/出入境检验检疫申报平台.xlsx";
        FileInputStream fileInputStream = null;

        try {
            fileInputStream = new FileInputStream(fileInput);
            Workbook workBook = WorkbookFactory.create( fileInputStream  );
            Sheet sheet  = workBook.getSheetAt(0) ;

            int nowRow = 0 ;
            int usefulRow = 0 ;
            int blankNum = 0;

            int START_COLOUM = 1;
            String SPACES = " ";
            String LINE_SEP = ",";
            String LINE_BREAK = "\n";


            StringBuilder table = new StringBuilder();


            //循环excel获取数据
            while( blankNum <= 3 ){

                Row row = sheet.getRow( nowRow );


                //空行继续
                if( row == null || row.getCell(START_COLOUM) == null || row.getCell(START_COLOUM ).getStringCellValue().isEmpty() ){
                    nowRow = nowRow + 1;
                    blankNum = blankNum + 1;

                    //遇到空行认为一个表的信息结束
                    if( table.toString().length() > 0 ){
                        String tableTemp = table.toString();
                        int tableLength = tableTemp.length();
                        //String createTable = tableTemp.substring(0,tableLength-1) + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
                        String createTable = tableTemp+ " PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
                        System.out.println(createTable );
                    }

                    //重置信息
                    usefulRow = 0;
                    table = new StringBuilder();

                    continue;
                }

                usefulRow = usefulRow + 1;

                String value = row.getCell(START_COLOUM ).getStringCellValue();

                if( usefulRow == 1 ){

                    //表名
                    table.append("CREATE TABLE " + value + " (");

                    nowRow = nowRow + 2;
                }else{

                    //字段名
                    table.append(LINE_BREAK).append(SPACES).append(value);

                    //类型
                    value = row.getCell(2 ).getStringCellValue();
                    table.append(SPACES).append( value);

                    //长度
                    if( row.getCell(3 ) != null ){
                        int l = (int)row.getCell(3 ).getNumericCellValue();
                        if( l > 0 ){
                            table.append( "(" + l + ") ");
                        }
                    }


                    //是否为空
                    if( row.getCell(4 ) != null ){
                        value = row.getCell(4 ).getStringCellValue();
                        if( "是".equals(value)){
                            table.append(SPACES).append( "NOT NULL");
                        }
                    }


                    //描述
                    value = row.getCell(6 ).getStringCellValue();
                    if( value != null && value.length() > 0  ){
                        table.append(SPACES).append( "COMMENT '" + value + "'" ).append( LINE_SEP  );
                    }

                    nowRow = nowRow + 1;
                }

            }




        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                if( fileInputStream!=null){
                    fileInputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    //读取一行的信息
    public static void getRowData( Row row , StringBuilder sb , List<Flight> flightList ) throws  Exception{



    }


}
